package com.szholly.pro.watergas_server.ps.service;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;

/**
 * 数据分析service
 * @author gislin
 * 2016年4月27日
 */
@Service
public class DataAnalysisService { 

	@Autowired
	DomainService domainService;
	
	@Autowired
	OnlineMonitorService onlineMonitorService;
	
	/**
	 * 获取月均值报表
	 * gislin
	 * 2016年4月27日
	 * @param mn 设备编号
	 * @param startDate 起始日期
	 * @param endDate 结束日期
	 * @param parmToChecks 要输出的污染物集合 格式：001,011,060,101
	 * @return
	 * @throws Exception
	 */
	public HashMap<String, Object> getMonthTrendReport(String mn,Date startDate,Date endDate,String parmToChecks) throws Exception {
		HashMap<String, Object> list = getTrendReport(mn, startDate, endDate, parmToChecks,"monitor_data_month");
		return list;
	}
	
	/**
	 * 获取年均值报表
	 * gislin
	 * 2016年4月27日
	 * @param mn 设备编号
	 * @param startDate 起始日期
	 * @param endDate 结束日期
	 * @param parmToChecks 要输出的污染物集合 格式：001,011,060,101
	 * @return
	 * @throws Exception
	 */
	public HashMap<String, Object> getYearTrendReport(String mn,Date startDate,Date endDate,String parmToChecks) throws Exception {
		
		HashMap<String, Object> list = getTrendReport(mn, startDate, endDate, parmToChecks,"monitor_data_year");
		return list;
	}
	
	private HashMap<String, Object> getTrendReport(String mn,Date startDate,Date endDate,String parmToChecks,String tableName){
		if(mn == null ||mn.equals("")){
			return null;
		}
		String parmItems = "";
		
		String[] parms = parmToChecks.split(",");
		
		for(String parmName : parms){
			parmItems += ",'"+parmName+"'";
		}
		parmItems = parmItems.replaceFirst(",", "");
		
		String dateTimeWhere = "";
		
		String[] yearHeadTexts = null;
		HashMap<String, Object> reportResult = new HashMap<>();
		
		if(tableName.equals("monitor_data_year")){
			
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDate);
			int startYear = cal.get(Calendar.YEAR);
			cal.setTime(endDate);
			int endYear = cal.get(Calendar.YEAR);
			int yearLength = endYear - startYear + 1;
			yearHeadTexts = new String[yearLength];
			for(int i=0;i<yearLength;i++){
				yearHeadTexts[i] = (startYear-i)+"年";
				dateTimeWhere += ",'"+(startYear-i)+"年"+"' P" + (startYear-i) + "年";
			}
		}
		else if(tableName.equals("monitor_data_month")){
			
			List<String> timeList = new ArrayList<>();
			
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDate);
			SimpleDateFormat sf = new SimpleDateFormat("YYYY年MM月");
			
			Calendar endCal = Calendar.getInstance();
			endCal.setTime(endDate);
			
			while(cal.before(endCal)){
				timeList.add(sf.format(cal.getTime()));
				dateTimeWhere += ",'"+sf.format(cal.getTime())+"' P" + sf.format(cal.getTime());
				cal.add(Calendar.MONTH, 1);
			}
			yearHeadTexts = timeList.toArray(new String[timeList.size()]);
		}
		
		dateTimeWhere = dateTimeWhere.replaceFirst(",", "");
		
		String sql = "SELECT * FROM ( select * from ( select DATE_TEXT,PARMNAME,avg from "+tableName+" where mn = @mn and datatime > @startDate and datatime <@endDate and parmname in ("+parmItems+") ) t \n"
				+ " pivot(sum(avg) for DATE_TEXT in ("+dateTimeWhere+"))\n"
						+ " ) t \n"
						+ " left join ("
						+ "select sp.name,sp.ABNORMAL_MIN,sp.ABNORMAL_Max from BC_SITE_POLL sp\n" +
							"       inner join BC_SITE_DEVICE_RPT  me on sp.device_id = me.guid\n" + 
							"       inner join BC_COLLECTION_INFO dc on dc.site_id = me.monitor_point\n" + 
							"       where dc.device_num = @mn"
						+ ") t1  on t1.name = t.parmname order by t.PARMNAME desc ";
		
		List<GlobalParameter> pc = new ArrayList<>();
		pc.add(new GlobalParameter("mn", mn));
		pc.add(new GlobalParameter("startDate", startDate));
		pc.add(new GlobalParameter("endDate", endDate));
		
		List<RowBase> list = DataUtil.getSelectRows(sql, pc);
		
		for(RowBase row : list){
			String pollName = row.getSafeString("PARMNAME");
			String pollText = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", pollName);
			String unitText = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", pollName);
			if(unitText != null && !"".equals(unitText)){
				pollText = pollText + "("+unitText+")";
			}
			row.setValue("PARMNAME", pollText);
		}
		
		reportResult.put("report", list);
		reportResult.put("yearHeadText",yearHeadTexts);
		
		return reportResult;
	}    
	
	/**
	 * 获取同比报表
	 * gislin
	 * 2016年4月29日
	 * @param mn
	 * @param startYear
	 * @param mq 如果是年同比，这里为空，如果是月同比，则是 1月、2月等，如果是季同比，则是1季、2季
	 * @param yearLength 同比的年数
	 * @param parmToChecks
	 * @param tableName
	 * @return
	 */
	public HashMap<String, Object> getX2XReport(String mn,int startYear,String mq,int yearLength,String parmToChecks,String tableName){
		if(mn == null ||mn.equals("")){
			return null;
		}
		String parmItems = "";
		
		String[] parms = parmToChecks.split(",");
		
		for(String parmName : parms){
			parmItems += ",'"+parmName+"'";
		}
		parmItems = parmItems.replaceFirst(",", "");
	
		String dateTimeWhere = "";
		String[] yearHeadTexts = new String[yearLength];
		for(int i=0;i<yearLength;i++){
			yearHeadTexts[i] = (startYear-i)+"年"+mq;
			dateTimeWhere += ",'"+(startYear-i)+"年"+mq+"' P" + (startYear-i) + "年"+mq;
		}
		
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, startYear - yearLength);
		cal.set(Calendar.MONTH, 1);
		cal.set(Calendar.DAY_OF_MONTH, 1);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		
		Date startDate = cal.getTime();
		
		cal.set(Calendar.YEAR, startYear);
		cal.set(Calendar.MONTH, 1);
		cal.set(Calendar.DAY_OF_MONTH, 1);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		
		Date endDate = cal.getTime();
		
		dateTimeWhere = dateTimeWhere.replaceFirst(",", "");
		
		String sql = "SELECT * FROM ( select * from ( select DATE_TEXT,PARMNAME,avg from "+tableName+" where mn = @mn and datatime > @startDate and datatime <@endDate and parmname in ("+parmItems+") ) t \n"
				+ " pivot(sum(avg) for DATE_TEXT in ("+dateTimeWhere+"))\n"
						+ " ) t order by PARMNAME desc ";
		List<GlobalParameter> pc = new ArrayList<>();
		pc.add(new GlobalParameter("mn", mn));
		pc.add(new GlobalParameter("startDate", startDate));
		pc.add(new GlobalParameter("endDate", endDate));
		
		List<RowBase> list = DataUtil.getSelectRows(sql, pc);
		
		if(list == null || list.size() ==0){
			return null;
		}
		
		HashMap<String, Object> reportResult = new HashMap<>();
		DecimalFormat df = new DecimalFormat("#0.00");
		
		for(RowBase row:list){
			String pollName = row.getSafeString("PARMNAME");
			String pollText = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", pollName);
			String unitText = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", pollName);
			if(unitText != null && !"".equals(unitText)){
				pollText = pollText + "("+unitText+")";
			}
			row.setValue("PARMNAME", pollText);
			double baseValue = row.getSafeDouble("P"+startYear+"年"+mq); 
			row.setValue("P"+startYear+"年"+mq, df.format(baseValue));// 格式化字符串，小数位不够三位补零
			if(baseValue == 0){
				continue;
			}
			
			for(int i=1;i<yearLength;i++){
				if(row.get("P"+(startYear-i)+"年"+mq) == null){
					row.remove("P"+(startYear-i)+"年"+mq);
					continue;
				}
				double value = row.getSafeDouble("P"+(startYear-i)+"年"+mq);
				row.setValue("P"+(startYear-i)+"年"+mq, df.format(value)); // 格式化字符串，小数位不够三位补零
				// ph不需要算百分比，只要差值
				if("001".equals(pollName)){
					double v = value - baseValue;
					row.setValue((startYear-i)+"年"+mq+"X2X_PRE", df.format(v));// 格式化字符串，小数位不够三位补零
				}
				else{
					double v = ((value - baseValue) / baseValue) * 100;
					row.setValue((startYear-i)+"年"+mq+"X2X_PRE", "("+df.format(v)+"%)");
				}
			}
		}
		
		reportResult.put("report", list);
		reportResult.put("yearHeadText",yearHeadTexts);
		
		return reportResult;
	}
	
}
